Step 1: Extract Data from the Enterprise OLTP Source to Staging Area
1. Drag and drop Execute SQL Task from the SSIS Toolbox onto the canvas
2. Configure the Execute SQL Task to initialize the staging tables by truncating all previous data by providing the query and screenshot above in the SQL Statement Tab.
3. Drag and drop the Data Flow Task from the SSIS Toolbox onto the canvas. Double-click to enter the edit mode in the Data Flow Task. Drag and Drop OLE DB source and destination tasks for 9 tables as shown in the screenshot below for the following tables:
· ADF_CCS_ClickThrough_Tracking
· ADF_CCS_ProjectQuestions
· ADF_CCS_ProjectRequest
· ADF_CCS_ProjectTags
· ADF_CCS_TagCategory
· ADF_CCS_Tags
· ADF_CCS_Tracking_Source
· ADF_Pre_Users
· ADF_Users
4. Configure each of the source and destination tasks as below for each of the above tables as below to load the staging tables:
Tutorial how to create a Simple SSIS package to load the OLE DB source to OLE DB destination